package com.springboot.film.dao;

import com.springboot.film.entity.vipinfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component(value = "vipinfoDao")
public interface vipinfoDao {

    //会员信息：vipinfo

    /**
     * 显示总数量
     * @return
     */
    @Select("select count(*) from vipinfo")
    int getCount();

    /**
     * 全部分页显示
     * @return
     */
    @Select("select a.*,b.name as u_name,FROM_UNIXTIME(a.start_time, '%Y-%m-%d') as start_time_temp," +
            "FROM_UNIXTIME(a.end_time, '%Y-%m-%d') as end_time_temp," +
            "DATEDIFF(FROM_UNIXTIME(a.end_time, '%Y-%m-%d'),FROM_UNIXTIME(a.start_time, '%Y-%m-%d')) AS all_time_temp" +
            " from vipinfo a,userinfo b where a.uid=b.id limit #{pos},#{size}")
    List<vipinfo> findByPage(@Param("pos")int pos, @Param("size")int size);

    /**
     * 显示搜索的总数量
     * @return
     */
    @Select("select count(*) from vipinfo a,userinfo b where a.uid=b.id and ${key} like concat(concat('%',#{name}),'%')")
    int getsearchCount(@Param("key")String key,@Param("name")String name);

    /**
     * 搜索并分页显示
     * @return
     */
    @Select("select a.*,b.name as u_name,FROM_UNIXTIME(a.start_time, '%Y-%m-%d') as start_time_temp," +
            "FROM_UNIXTIME(a.end_time, '%Y-%m-%d') as end_time_temp," +
            "DATEDIFF(FROM_UNIXTIME(a.end_time, '%Y-%m-%d'),FROM_UNIXTIME(a.start_time, '%Y-%m-%d')) AS all_time_temp" +
            " from vipinfo a,userinfo b where a.uid=b.id and" +
            " ${key} like concat(concat('%',#{name}),'%') limit #{pos},#{size}")
    List<vipinfo> findsearchByPage(@Param("key")String key,@Param("name")String name,@Param("pos")int pos, @Param("size")int size);

    /**
     * 新增
     */
    @Insert("INSERT INTO vipinfo(uid,start_time,end_time,all_time) " +
            "VALUES(#{uid},UNIX_TIMESTAMP(#{start_time_temp}),UNIX_TIMESTAMP(#{end_time_temp})," +
            "DATEDIFF(FROM_UNIXTIME(end_time, '%Y-%m-%d'),FROM_UNIXTIME(start_time, '%Y-%m-%d')))")
    void add(vipinfo vipinfo);

    /**
     * 删除
     */
    @Delete("DELETE FROM vipinfo WHERE ID=#{id}")
    void delete(@Param("id")int id);

    /**
     * 修改(先找到并显示，再修改)
     */
    @Select("select a.*,b.name as u_name,FROM_UNIXTIME(a.start_time, '%Y-%m-%d') as start_time_temp," +
            "FROM_UNIXTIME(a.end_time, '%Y-%m-%d') as end_time_temp" +
            " from vipinfo a,userinfo b where a.uid=b.id and a.id=#{id}")
    List<vipinfo> getUserone(@Param("id")int id);

    @Update("update vipinfo set uid=#{uid},start_time=UNIX_TIMESTAMP(#{start_time_temp})," +
            "end_time=UNIX_TIMESTAMP(#{end_time_temp}),all_time=#{all_time} where id=#{id}")
    void update(vipinfo vipinfo);

    /**
     * 修改vip
     */
    @Update("update vipinfo c,userinfo d set d.vip=if((select unix_timestamp(now()) from " +
            "(select unix_timestamp(now())) as a)>(select end_time from (select end_time from vipinfo where uid=#{uid}) " +
            "as b),0,1) where c.uid=#{uid} and c.uid=d.id;")
    void updatevip(vipinfo vipinfo);

    /**
     * 每天自动查找是否过期vip
     */
    @Select("select max(id) from vipinfo;")
    int search_id();
    @Update("update vipinfo c,userinfo d set d.vip=if((select unix_timestamp(now()) from " +
            "(select unix_timestamp(now())) as a)>(select end_time from (select end_time from vipinfo where id=#{id}) " +
            "as b),0,1) where c.id=#{id} and c.uid=d.id;")
    void updatevipday(@Param("id")int id);
    @Update("update vipinfo c,userinfo d set d.vip=0 where c.id=#{id} and c.uid=d.id;")
    void updatevipdelete(@Param("id")int id);

}
